<?php

namespace App\Modules\Trade\Models;

use Illuminate\Database\Eloquent\Model;
use DB;
use Carbon\Carbon;

class OrderDayStat extends Model
{
    protected $table = 'order_day_stats';

    protected $fillable = ['distribution_channel_id', 'channel_user_id', 'distribution_channel_name', 'date', 'pay_success_user_num', 'ticket_recharge_user_num', 'year_recharge_user_num', 'total_recharge_amount', 'unpaid_num', 'paid_num', 'promotion_total_uv', 'promotion_total_pv', 'force_user_num', 'ticket_recharge_paid_num', 'ticket_recharge_unpaid_num', 'ticket_recharge_amount', 'year_recharge_paid_num', 'year_recharge_unpaid_num', 'year_recharge_amount', 'send_order_num', 'real_send_order_num', 'register_user_num', 'first_recharge_user_num', 'first_recharge_amount', 'month', 'reg_user_first_recharge_amount', 'reg_user_first_recharge_user_num', 'fee', 'charge_balance', 'reward_balance', 'real_register_user_num', 'channel_merchant_recharge_amount'];

    static function accountSearch($params, $is_all)
    {
        $search_obj = self::join('distribution_channels', 'distribution_channels.id', '=', 'order_day_stats.distribution_channel_id')
            ->join('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id')
            ->join('companies', 'companies.id', '=', 'channel_users.company_id')
            ->select("order_day_stats.date",
                DB::raw("sum(order_day_stats.pay_success_user_num) as pay_success_user_num"),
                DB::raw("sum(order_day_stats.ticket_recharge_user_num) as ticket_recharge_user_num"),
                DB::raw("sum(order_day_stats.year_recharge_user_num) as year_recharge_user_num"),
                DB::raw("sum(order_day_stats.total_recharge_amount) as total_recharge_amount"),
                DB::raw("sum(order_day_stats.unpaid_num) as unpaid_num"),
                DB::raw("sum(order_day_stats.paid_num) as paid_num"),
                DB::raw("sum(order_day_stats.promotion_total_uv) as promotion_total_uv"),
                DB::raw("sum(order_day_stats.promotion_total_pv) as promotion_total_pv"),
                DB::raw("sum(order_day_stats.force_user_num) as force_user_num"),
                DB::raw("sum(order_day_stats.old_user_subscribe_num) as old_user_subscribe_num"),
                "companies.name as company_name",
                "companies.fans_gender");

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            if (is_array($params['distribution_channel_id']) && count($params['distribution_channel_id'])) {
                $search_obj->whereIn('order_day_stats.distribution_channel_id', $params['distribution_channel_id']);
            } else {
                $search_obj->where('order_day_stats.distribution_channel_id', $params['distribution_channel_id']);
            }
        }
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('order_day_stats.date', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('order_day_stats.date', '<=', $params['end_time']);

        $search_obj->groupBy('order_day_stats.date');
        $search_obj->orderBy('order_day_stats.date', 'desc');

        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    static function search($params, $is_all)
    {
        $search_obj = self::join('distribution_channels', 'distribution_channels.id', '=', 'order_day_stats.distribution_channel_id')
            ->join('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id')
            ->join('companies', 'companies.id', '=', 'channel_users.company_id')
            ->select(DB::raw("order_day_stats.*,companies.name as company_name,companies.fans_gender"));

        if (isset($params['orderBy']) && $params['orderBy']) {
            $search_obj->orderBy('order_day_stats.' . $params['orderBy'], 'desc');
        } else {
            $search_obj->orderBy('order_day_stats.id', 'desc');
        }
        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            if (is_array($params['distribution_channel_id']) && count($params['distribution_channel_id'])) {
                $search_obj->whereIn('order_day_stats.distribution_channel_id', $params['distribution_channel_id']);
            } else {
                $search_obj->where('order_day_stats.distribution_channel_id', $params['distribution_channel_id']);
            }
        }
        if (isset($params['begin_time']) && $params['begin_time']) $search_obj->where('order_day_stats.date', '>=', $params['begin_time']);
        if (isset($params['end_time']) && $params['end_time']) $search_obj->where('order_day_stats.date', '<=', $params['end_time']);


        if ($is_all) {
            return $search_obj->get();
        } else {
            return $search_obj->paginate();
        }
    }

    /**
     * 获取渠道汇总统计数据
     * @param  int $distribution_channel_id 分销渠道ID
     * @return mixed
     */
    static function getSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)->first();
    }

    /**
     * 获取昨日渠道汇总统计数据
     * @param $distribution_channel_id
     * @return mixed
     */
    static function getYesterdaySumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        $yesterday = date("Y-m-d", strtotime('-1 day'));
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)->where('date', '=', $yesterday)->first();
    }

    /**
     * 获取当月渠道汇总统计数据
     * @param $distribution_channel_id
     * @return mixed
     */
    static function getCurrentMonthSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        $begin_date = date("Y-m") . '-1';
        $end_date = date("Y-m", strtotime('+1 month')) . '-1';
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)
            ->where('date', '>=', $begin_date)
            ->where('date', '<', $end_date)
            ->first();
    }

    /**
     * 获取上月站点汇总统计数据
     * @param $distribution_channel_id
     * @return mixed
     */
    static function getLastMonthSumByChannelId($distribution_channel_id)
    {
        $fields = [
            DB::raw("sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];

        $begin_date = date("Y-m", strtotime('-1 month')) . '-1';
        $end_date = date("Y-m") . '-1';
        return self::select($fields)->where('distribution_channel_id', $distribution_channel_id)
            ->where('date', '>=', $begin_date)
            ->where('date', '<', $end_date)
            ->first();
    }

    //根据渠道获取合计
    static function getStatByChannel($params)
    {
        $fields = [
            DB::raw("date,
                     sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(real_register_user_num) real_register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount,
                     sum(reg_user_first_recharge_amount) reg_user_first_recharge_amount,
                     sum(reg_user_first_recharge_user_num) reg_user_first_recharge_user_num,
                     sum(channel_merchant_recharge_amount) channel_merchant_recharge_amount
                     ")
        ];

        $begin_time = $params['begin_time'];
        $end_time = $params['end_time'];
        return self::select($fields)
            ->where('date', '>=', $begin_time)
            ->where('date', '<=', $end_time)
            ->orderBy('date', 'desc')
            ->groupBy('date')
            ->get();
    }

    static function getAllGroupbyMonth()
    {
        $fields = [
            DB::raw("month,
                     sum(pay_success_user_num) pay_success_user_num,
                     sum(total_recharge_amount) total_recharge_amount,
                     sum(unpaid_num) unpaid_num,
                     sum(paid_num) paid_num,
                     sum(promotion_total_uv) promotion_total_uv,
                     sum(promotion_total_pv) promotion_total_pv,
                     sum(force_user_num) force_user_num,
                     sum(ticket_recharge_user_num) ticket_recharge_user_num,
                     sum(ticket_recharge_paid_num) ticket_recharge_paid_num,
                     sum(ticket_recharge_unpaid_num) ticket_recharge_unpaid_num,
                     sum(ticket_recharge_amount) ticket_recharge_amount,
                     sum(year_recharge_user_num) year_recharge_user_num,
                     sum(year_recharge_paid_num) year_recharge_paid_num,
                     sum(year_recharge_unpaid_num) year_recharge_unpaid_num,
                     sum(year_recharge_amount) year_recharge_amount,
                     sum(send_order_num) send_order_num,
                     sum(real_send_order_num) real_send_order_num,
                     sum(register_user_num) register_user_num,
                     sum(real_register_user_num) real_register_user_num,
                     sum(first_recharge_user_num) first_recharge_user_num,
                     sum(first_recharge_amount) first_recharge_amount
                     ")
        ];
        return self::select($fields)->groupBy('month')->orderBy('month', 'desc')->orderBy('total_recharge_amount', 'desc')->get();
    }

    static function getRechargeByChannel($distribution_channel, $time_range)
    {
        $distribution_channels = explode(',', $distribution_channel);
        return self::getRechargeByChannels($distribution_channels, $time_range);
    }

    static function getRechargeByChannels($distribution_channels, $time_range)
    {
        $obj = self::whereIn('distribution_channel_id', $distribution_channels);
        if (isset($time_range['begin_time']) && $time_range['begin_time']) {
            $obj->where('date', '>=', $time_range['begin_time']);
        }
        if (isset($time_range['end_time']) && $time_range['end_time']) {
            $obj->where('date', '<=', $time_range['end_time']);
        }
        return $obj->sum('total_recharge_amount');
    }

    public static function getSiteYesterdayChargeInfo($distribution_channel_ids, $date)
    {
        $obj = self::join('distribution_channels', 'distribution_channels.id', '=', 'order_day_stats.distribution_channel_id')
            ->join('channel_users', 'channel_users.id', '=', 'distribution_channels.channel_user_id');

        $obj->where('order_day_stats.date', $date);
        $obj->whereIn('order_day_stats.distribution_channel_id', $distribution_channel_ids);
        $obj->orderBy('order_day_stats.total_recharge_amount', 'desc');
        $obj->select('order_day_stats.distribution_channel_id', 'order_day_stats.total_recharge_amount', 'channel_users.phone');
        return $obj->get();
    }
}

